#include "SqlManager.h"

//Qt libraries
#include <QMap>
#include <QSqlQuery>
#include <QMapIterator>
#include <QVector>

//BlackBear code
#include "Bear.h"
#include "Error.h"
#include "BBTable.h"
#include "Util.h"

SqlManager* SqlManager::m_instance = NULL;
int SqlManager::m_connectionNumber = 0;

QString ATTRIBUTE_TYPE_COMPOSITE = "Composite";

QString COLUMN_ID = "id";
QString COLUMN_ID_PARENT = "parentId";


SqlManager* SqlManager::instance()
{
	//Check if is the first time using
	if( m_instance == NULL )
	{
		m_instance = new SqlManager();
	}

	return m_instance;
}

BlackBear* SqlManager::connect( const QString& dataBaseName, const QString& userName, const QString& password, const QString& hostName )
{
	QString connectionName( getNewConnectionName() );
	QSqlDatabase* dataBase = new QSqlDatabase( QSqlDatabase::addDatabase("QSQLITE", connectionName ) );

    dataBase->setHostName( hostName );
    dataBase->setDatabaseName( dataBaseName );
	dataBase->setUserName( userName );
    dataBase->setPassword( password );

	//Check if database is open
	if( !dataBase->open() )
	{
		return NULL;
	}

	return new BlackBear( dataBase, connectionName );
}

bool SqlManager::createDataBase( const QString& dataBaseName, const QString& userName, const QString& password, const QString& hostName )
{
	QString sqlQuery = QString( "CREATE DATABASE IF NOT EXISTS %1;" )
		.arg( dataBaseName );

	sqlQuery = QString( "%1 CREATE USER %2 IDENTIFIED BY %3; " )
		.arg( sqlQuery )
		.arg( userName )
		.arg( password );

	QString connectionName( "BBCreateDataBaseConnection" );
	QSqlDatabase* dataBase = new QSqlDatabase( QSqlDatabase::addDatabase( "QSQLITE", connectionName ));
	QSqlQuery query( *dataBase );

	if( !query.exec( sqlQuery ) )
	{
		ERR_ABORT( "Not possible to execute query. Database was not created" );
		return false;
	}

	dataBase->removeDatabase( connectionName );

	return true;

	/*AT I
	TODO: Verificar se é necessario incluir essas permissoes para o usuario recem criado

	GRANT USAGE ON * . * TO 'superAdmin' IDENTIFIED BY '1234' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;
	GRANT ALL PRIVILEGES ON `sctdb` . * TO 'superAdmin' WITH GRANT OPTION ;
	 AT F*/
}

bool SqlManager::createTable( BlackBear& blackBear, BBTable& table )
{
	QString sqlQuery = QString("CREATE TABLE %1 (" )
	.arg( table.getName() );

	//Create column id
	sqlQuery = QString( "%1%2 INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT" )
		.arg( sqlQuery )
		.arg( COLUMN_ID );

	//Create column id for parent 
	sqlQuery = QString( "%1,%2 INTEGER" )
		.arg( sqlQuery )
		.arg( COLUMN_ID_PARENT );

	QMapIterator<QString,QString> i( table.getColumns() );
	QString sqlCompositeQuery;

	//Get all column from object
	while( i.hasNext() )
	{
		i.next();

		//Check if current column has a composite or primitive type. Compositions are always the last columns in table
		if ( !blackBear.isComposite( i.value() ) )
		{
			sqlQuery = QString( "%1,%2 %3" )
				.arg( sqlQuery )
				.arg( i.key())
				.arg( getSQLVariableType( i.value() ) );
		}
		else
		{
			sqlCompositeQuery = QString( "%1,%2 %3" )
				.arg( sqlCompositeQuery )
				.arg( i.key())
				.arg( getSQLVariableType( ATTRIBUTE_TYPE_COMPOSITE ) );
		}
	}

	sqlQuery = QString("%1%2);")
	.arg( sqlQuery )
	.arg( sqlCompositeQuery );

	QSqlQuery query( *blackBear.m_dataBase );

	//Execute Sql code, creating the table
	if( !query.exec( sqlQuery ) )
	{
		ERR_ABORT( "Not possible to execute query." );
		return false;
	}

	return true;
}

QString SqlManager::getNewConnectionName()
{
	//Create a new name for the connection
	return QString("%1%2")
		.arg( "connection" )
		.arg( getConnectionNumber() );
}

bool SqlManager::insertRow( BlackBear& blackBear, BBObject& object )
{
	QString sqlQuery = QString( "INSERT INTO %1 VALUES (" ).arg( object.getName() );

	//Set id column with null. The table will set object id with last id value.
	sqlQuery = QString("%1null,%2")
		.arg( sqlQuery )
		.arg( object.getParentId() );

	QMapIterator<QString, QVariant> iteratorPrimitive( object.getAttributesPrimitiveValues() );

	//Get all primitives attributes values
	while( iteratorPrimitive.hasNext() )
	{
		iteratorPrimitive.next();
		QString currentKey = iteratorPrimitive.key();
	
		sqlQuery = QString("%1,%2")
			.arg( sqlQuery )
			.arg( convertToSQLFormat( object.getAttributeType( currentKey ), iteratorPrimitive.value() ) );
	}

	QMapIterator<QString, QVariant> iteratorComposite( object.getAttributesCompositionValues() );

	//Get all composite attributes values
	while( iteratorComposite.hasNext() )
	{
		QVariant currentValue = iteratorComposite.next().value();
		int idObject = blackBear.saveComposite( currentValue.value<Bear>() );

		sqlQuery = QString("%1,%2")
			.arg( sqlQuery )
			.arg( idObject );
	}

	sqlQuery = QString("%1);")
		.arg( sqlQuery );

	QSqlQuery query( *blackBear.m_dataBase );

	//Execute query
	if( !query.exec( sqlQuery ) )
	{
		ERR_ABORT( "Not possible to execute query." );
		return false;
	}

	//Set object id with his position in table
	object.setId( getLastIdFromTable( blackBear, object ) );

	return true;
}

bool SqlManager::updateRow( BlackBear& blackBear, BBObject& object )
{
	QString sqlQuery = QString( "UPDATE %1 SET" ).arg( object.getName() );
	QMapIterator<QString, QVariant> iteratorPrimitive( object.getAttributesPrimitiveValues() );
	bool hasPrimitive = false;

	//Get first primitive attribute
	if( iteratorPrimitive.hasNext() )
	{
		iteratorPrimitive.next();
		QString currentKey = iteratorPrimitive.key();

		sqlQuery = QString( "%1 %2=%3" )
			.arg( sqlQuery )
			.arg( currentKey )
			.arg( convertToSQLFormat( object.getAttributeType( currentKey ), iteratorPrimitive.value() ) );

		//Get all primitive attributes values, less the first 
		while( iteratorPrimitive.hasNext() )
		{
			iteratorPrimitive.next();
			currentKey = iteratorPrimitive.key();

			sqlQuery = QString( "%1,%2=%3" )
				.arg( sqlQuery )
				.arg( currentKey )
				.arg( convertToSQLFormat( object.getAttributeType( currentKey ), iteratorPrimitive.value() ) );
		}

		hasPrimitive = true;
	}

	QMapIterator<QString, QVariant> iteratorComposite( object.getAttributesCompositionValues() );

	//Check if any primitive was add in query. If not, composite attribute have to begin the query with no comman
	if ( !hasPrimitive && iteratorComposite.hasNext() )
	{
		QVariant currentValue = iteratorComposite.next().value();
		int idObject = blackBear.saveComposite( currentValue.value<Bear>() );

		sqlQuery = QString( "%1 %2=%3" )
			.arg( sqlQuery )
			.arg( iteratorComposite.key() )
			.arg( idObject );
	}

	//Get all composite attributes values
	while( iteratorComposite.hasNext() )
	{
		QVariant currentValue = iteratorComposite.next().value();
		int idObject = blackBear.saveComposite( currentValue.value<Bear>() );

		sqlQuery = QString( "%1,%2=%3" )
			.arg( sqlQuery )
			.arg( iteratorPrimitive.key() )
			.arg( idObject );
	}

	sqlQuery = QString( "%1 WHERE %2=%3;" )
		.arg( sqlQuery )
		.arg( COLUMN_ID )
		.arg( object.id() );

	QSqlQuery query( *blackBear.m_dataBase );

	//Execute query
	if( !query.exec( sqlQuery ) )
	{
		ERR_ABORT( "Not possible to execute query." );
		return false;
	}

	return true;
}

bool SqlManager::deleteRow( BlackBear& blackBear, BBObject& object )
{
	QVariant currentValue;
	QMapIterator<QString, QVariant> iteratorComposite( object.getAttributesCompositionValues() );

	//Delete all composites
	while( iteratorComposite.hasNext() )
	{
		currentValue = iteratorComposite.next().value();
		blackBear.deleteObject( currentValue.value<Bear>() );
	}

	//Delete the object
	QString sqlQuery = QString( "DELETE FROM %1 WHERE %2 = %3;" )
		.arg( object.getName() )
		.arg( COLUMN_ID )
		.arg( object.id() );

	QSqlQuery query( *blackBear.m_dataBase );

	//Execute query
	if( !query.exec( sqlQuery ) )
	{
		ERR_ABORT( "Not possible to execute query." );
		return false;
	}

	//Object has his id setted with the default value
	object.setId( BBObject::ATTRIBUTE_ID_VALUE_DEFAULT );

	return true;
}

bool SqlManager::selectRow( BlackBear& blackBear, BBObject& object )
{
	QSqlQuery query( *blackBear.m_dataBase );
	QString sqlQuery = QString("SELECT * FROM %1 WHERE %2=%3")
		.arg( object.getName() )
		.arg( COLUMN_ID )
		.arg( object.id() );

	//Execute query
	if( !query.exec( sqlQuery ) )
	{
		ERR_ABORT( "Not possible to execute query." );
		return false;
	}
	
	//Get row
	query.first();

	//First index is the object's id, that we already know. So begin from second index, parent's id
	int currentIndex = 1;
	
	//Set parent's id and increment one in index
	object.setParentId( query.value( currentIndex++ ).toInt() );
	
	QMapIterator<QString, QVariant> iteratorPrimitive( object.getAttributesPrimitiveValues() );

	//Get all primitive column and include in object
	while( iteratorPrimitive.hasNext() )
	{
		object.setAttributePrimitiveValue( iteratorPrimitive.next().key(), query.value( currentIndex ) );
		currentIndex++;
	}

	QMapIterator<QString, QVariant> iteratorComposite( object.getAttributesCompositionValues() );

	//Get all composite column and include in object
	while( iteratorComposite.hasNext() )
	{
		QVariant bear;

		bear.setValue( *blackBear.load( iteratorComposite.next().key(), query.value( currentIndex++ ).toInt() ) );
		object.setAttributeCompositionValue( iteratorComposite.next().key(), bear );
	}
	
	return true;
}

QString SqlManager::convertToSQLFormat( const QString type, const QVariant value )
{
	QString ret;

	//Check if attribute is a string or character. In this case, needs quotes for sql format
	if( ATTRIBUTE_TYPE_STRING == type ||
		ATTRIBUTE_TYPE_QSTRING == type ||
		ATTRIBUTE_TYPE_CHAR == type ||
		ATTRIBUTE_TYPE_UCHAR == type ||
		ATTRIBUTE_TYPE_UNSIGNED_CHAR == type )
	{
		ret = QString( "\'" + value.toString() + "\'" );
	}
	//Check if attribute is a boolean.
	else if( ATTRIBUTE_TYPE_BOOL == type )
	{
		if( value.toBool() )
			ret = QString( "1" );
		else
			ret = QString( "0" );
	}
	else
	{
		ret = value.toString();
	}

	return ret;
}

/*
QString SqlManager::buildAttributeValueStringByType( QVariant value, QString type )
{
	QString ret( value.toString() );

	if ( ATTRIBUTE_TYPE_QSTRING == type ||
		 ATTRIBUTE_TYPE_STRING == type )
	{
		ret = QString( "\'%1\'" ).arg( ret );
	}
	
	return ret;
}
*/

QString SqlManager::getSQLVariableType( const QString& type )
{
	QString ret;

	//Check if type is a interger
	if( ATTRIBUTE_TYPE_SHORT == type ||
		ATTRIBUTE_TYPE_USHORT == type ||
		ATTRIBUTE_TYPE_SHORT_INT == type ||
		ATTRIBUTE_TYPE_UNSIGNED_SHORT == type ||
		ATTRIBUTE_TYPE_UNSIGNED_SHORT_INT == type ||
		ATTRIBUTE_TYPE_INT == type ||
		ATTRIBUTE_TYPE_UINT == type ||
		ATTRIBUTE_TYPE_UNSIGNED_INT == type ||
		ATTRIBUTE_TYPE_LONG == type ||
		ATTRIBUTE_TYPE_ULONG == type ||
		ATTRIBUTE_TYPE_LONG_INT == type ||
		ATTRIBUTE_TYPE_UNSIGNED_LONG == type ||
		ATTRIBUTE_TYPE_UNSIGNED_LONG_INT == type ||
		ATTRIBUTE_TYPE_WIDE_CHARACTER == type )
	{
		ret = QString( "INTERGER" );
	}
	//Check if type is a string
	else if( ATTRIBUTE_TYPE_CHAR == type ||
			 ATTRIBUTE_TYPE_UCHAR == type ||
			 ATTRIBUTE_TYPE_UNSIGNED_CHAR == type ||
			 ATTRIBUTE_TYPE_STRING == type ||
			 ATTRIBUTE_TYPE_QSTRING == type )
	{
		ret = QString( "TEXT" );
	}
	//Check if type is a floating point
	else if ( ATTRIBUTE_TYPE_FLOAT == type ||
			  ATTRIBUTE_TYPE_DOUBLE == type ||
			  ATTRIBUTE_TYPE_LONG_DOUBLE == type )
	{
		ret = QString( "REAL" );
	}
	//Check if type is a boolean
	else if ( ATTRIBUTE_TYPE_BOOL == type )
	{
		ret = QString( "INTERGER" );
	}
	//If type isn't any primitive type, so it's a compoite
	else if( ATTRIBUTE_TYPE_COMPOSITE == type )
	{
		//TODO: Talvez utilizar o objeto do blackbear para verificar se o tipo é de algum objeto criado pelo usuario
		ret = QString( "INTERGER" );
	}
	else
	{
		ERR_ABORT( "Attribute's type unknown." );
	}

	return ret;
}

int SqlManager::getLastIdFromTable( BlackBear& blackBear, BBObject& object )
{
	QSqlQuery query( *blackBear.m_dataBase );
	QString tableName( object.getName() );

	QString sqlQuery = QString("SELECT MAX(%1) FROM %2")
		.arg( COLUMN_ID )
		.arg( tableName );

	if( !query.exec( sqlQuery ) )
	{
		ERR_ABORT( "Not possible to execute query." );
	}
	
	if( !query.first() )
	{
		ERR_ABORT( "Not possible to get data from the database." );
	}
	 
	return query.value( 0 ).toInt();
}
/*
int SqlManager::countRows( BlackBear& blackBear, QString& tableName )
{
	ERR_ASSERT( blackBear.m_dataBase != NULL );

	QSqlQuery query( *blackBear.m_dataBase );
	QString sqlQuery = QString("SELECT COUNT(*) FROM %1")
		.arg( tableName );

	if( !query.exec( sqlQuery ) )
	{
		ERR_ABORT( "Não foi possível executar a query." );
	}

	if( !query.first() )
	{
		ERR_ABORT( "Não foi possível buscar o numero de linhas da tabela" );
	}

	int numberRows = query.value( 0 ).toInt();

	ERR_ASSERT( numberRows >= 0 );

	return numberRows;
}
*/

int SqlManager::countRows( BlackBear& blackBear, QString& tableName )
{
	QSqlQuery query( *blackBear.m_dataBase );

	QString sqlQuery = QString("SELECT COUNT(*) FROM %1")
		.arg( tableName );

	if( !query.exec( sqlQuery ) )
	{
		ERR_ABORT( "Not possible to execute query." );
	}

	if( !query.first() )
	{
		ERR_ABORT( "Not possible to get data from the database." );
	}

	return query.value( 0 ).toInt();
}

QVector<int> SqlManager::getIds( BlackBear& blackBear, QString& tableName )
{
	QSqlQuery query( *blackBear.m_dataBase );
	QString sqlQuery = QString("SELECT %1 FROM %2")
		.arg( COLUMN_ID )
		.arg( tableName );

	if( !query.exec( sqlQuery ) )
	{
		ERR_ABORT( "Not possible to execute query." );
	}

	//QVector<int>* ids = new QVector<int>( countRows( blackBear, tableName ) );
	QVector<int> ids( countRows( blackBear, tableName ) );

	ERR_ASSERT( ids.count() >= 0 );

	for ( int i = 0; query.next(); i++ )
	{
		ids[i] = query.value( 0 ).toInt();
	}

	return ids;
}	
